package ipt.dao.f4;

import ipt.exp.AlumnoException;
import ipt.vo.f4.Curso;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CursoDAO {

	Connection connection = null;
	PreparedStatement ptmt = null;
	ResultSet resultSet = null;

	private final static String TABLE_NAME = "CURSO";
	// Columnas
	private final static String COL_ID = "id";
	private final static String COL_NAME = "name";
	private final static String COL_DESCRIPTION = "description";
	private final static String COL_IMAGEN = "imagen";
	private final static String COL_DOCUMENTACION = "documentacion";
	private final static String COL_NUM_MAX_ALUM = "num_max_alum";

	public CursoDAO() {
		super();
	}

	/**
	 * Conecta a la Base de datos
	 * 
	 * @return
	 * @throws SQLException
	 */
	protected Connection getConnection() throws SQLException {
		Connection conn;
		conn = ConnectionFactory.getInstance().getConnection();
		return conn;
	}

	/**
	 * Saca el listado completo de curso de la Base de Datos y la mete en un
	 * List
	 * 
	 * @return List<curso> devuelve un listado de cursos
	 */
	public List<Curso> getCursos() {

		List<Curso> listaCur = new ArrayList<Curso>();

		try {

			String queryString = "SELECT * FROM " + TABLE_NAME;

			connection = getConnection();

			ptmt = connection.prepareStatement(queryString);

			resultSet = ptmt.executeQuery();

			while (resultSet.next()) {

				Curso cur = new Curso();
				cur.setId(resultSet.getInt(COL_ID));
				cur.setName(resultSet.getString(COL_NAME));
				cur.setDescription(resultSet.getString(COL_DESCRIPTION));
		//		cur.setImagen(resultSet.getBlob(COL_IMAGEN));
				cur.setDocumentacion(resultSet.getClob(COL_DOCUMENTACION));
				cur.setNum_max_alum(resultSet.getInt(COL_NUM_MAX_ALUM));

				listaCur.add(cur);
				cur = null;

			}

			return listaCur;

		} catch (SQLException e) {
			System.out.println("\n--- SQLException caught ---\n");
			while (e != null) {
				System.out.println("Message: " + e.getMessage() + "\n");
				System.out.println("SQLState: " + e.getSQLState() + "\n");
				System.out.println("ErrorCode: " + e.getErrorCode() + "\n");
				e = e.getNextException();
				System.out.println("");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

			try {
				if (resultSet != null)
					resultSet.close();
				if (ptmt != null)
					ptmt.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				System.out.println("\n--- SQLException caught ---\n");
				while (e != null) {
					System.out.println("Message: " + e.getMessage() + "\n");
					System.out.println("SQLState: " + e.getSQLState() + "\n");
					System.out.println("ErrorCode: " + e.getErrorCode() + "\n");
					e = e.getNextException();
					System.out.println("");
				}
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return listaCur;
	}

	/**
	 * busca en la Base de Datos los datos de un curso por id.
	 * 
	 * @param id
	 *            del curso
	 * @return Curso
	 * @throws Exception
	 */

	public Curso getCursoById(int id) throws Exception {
		try {
			List<Curso> listaCur = new ArrayList<Curso>();
			String queryString = "SELECT * FROM " + TABLE_NAME + " WHERE "
					+ COL_ID + "='" + id + "'";
			connection = getConnection();
			ptmt = connection.prepareStatement(queryString);
			resultSet = ptmt.executeQuery();
			Curso cur = new Curso();
//			byte[] img;
			while (resultSet.next()) {
				cur.setId(resultSet.getInt(COL_ID));
				cur.setName(resultSet.getString(COL_NAME));
				cur.setDescription(resultSet.getString(COL_DESCRIPTION));
//				img=resultSet.getBytes(COL_IMAGEN);
				cur.setDocumentacion(resultSet.getClob(COL_DOCUMENTACION));
				cur.setNum_max_alum(resultSet.getInt(COL_NUM_MAX_ALUM));
				//cur.setImagen(getImage(img,true));
				listaCur.add(cur);
			}
			
							
			if (listaCur.size() > 1) {

				throw (new Exception(
						"ERROR: Existen mas de un curso con el id:" + id));
			} else if (listaCur.size() == 1) {

				return cur;
			} else {
				return null;
			}

		} catch (AlumnoException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			System.out.println("\n--- SQLException caught ---\n");
			while (e != null) {
				System.out.println("Message: " + e.getMessage() + "\n");
				System.out.println("SQLState: " + e.getSQLState() + "\n");
				System.out.println("ErrorCode: " + e.getErrorCode() + "\n");
				e = e.getNextException();
				System.out.println("");
			}
		} finally {

			try {
				if (resultSet != null)
					resultSet.close();
				if (ptmt != null)
					ptmt.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				System.out.println("\n--- SQLException caught ---\n");
				while (e != null) {
					System.out.println("Message: " + e.getMessage() + "\n");
					System.out.println("SQLState: " + e.getSQLState() + "\n");
					System.out.println("ErrorCode: " + e.getErrorCode() + "\n");
					e = e.getNextException();
					System.out.println("");
				}
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return null;
	}
/*
	private static Image getImage(byte[] bytes, boolean isThumbnail) throws IOException {

	    ByteArrayInputStream bis = new ByteArrayInputStream(bytes);
	    Iterator readers = ImageIO.getImageReadersByFormatName("jpeg");
	    ImageReader reader = (ImageReader) readers.next();
	    Object source = bis; // File or InputStream
	    ImageInputStream iis = ImageIO.createImageInputStream(source);
	    reader.setInput(iis, true);
	    ImageReadParam param = reader.getDefaultReadParam();
	    if (isThumbnail) {

	        param.setSourceSubsampling(4, 4, 0, 0);

	    }
	    return reader.read(0, param);

	}
	*/
	
	/**
	 * inserta en la Base de Datos los datos a un alumno
	 * 
	 * @param alu
	 *            un alumno
	 * @return boolean
	 */
	
	public boolean insertCurso(Curso cur)  {
		try {
			
			
				String queryString = "INSERT INTO " + TABLE_NAME + "("
						+ COL_NAME + "," + COL_DESCRIPTION + "," + COL_IMAGEN
						+ "," + COL_NUM_MAX_ALUM + ")VALUES ('" + cur.getName() + "','"
						+ cur.getDescription() + "','" + cur.getImagen() +"'," + cur.getNum_max_alum()+ ")";
				connection = getConnection();
				ptmt = connection.prepareStatement(queryString);
				ptmt.executeUpdate();
			

		} catch (SQLException e) {
			System.out.println("\n--- SQLException caught ---\n");
			while (e != null) {
				System.out.println("Message: " + e.getMessage() + "\n");
				System.out.println("SQLState: " + e.getSQLState() + "\n");
				System.out.println("ErrorCode: " + e.getErrorCode() + "\n");
				e = e.getNextException();
				System.out.println("");
			}
		} finally {

			try {
				if (ptmt != null)
					ptmt.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				System.out.println("\n--- SQLException caught ---\n");
				while (e != null) {
					System.out.println("Message: " + e.getMessage() + "\n");
					System.out.println("SQLState: " + e.getSQLState() + "\n");
					System.out.println("ErrorCode: " + e.getErrorCode() + "\n");
					e = e.getNextException();
					System.out.println("");
				}
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return false;

	}
	
	
}
